﻿using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using System.Data;
using System.Data.SqlClient;
using HZ.Common;
namespace HZ.DrugTrade.DAL
{
    public class DAL_ProcurementProject
    {


        /// <summary>
        /// 新增一个采购项目
        /// </summary>
        public static int AddProcurementProject(ProcurementProject project)
        {

            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@in_ProjectID", project.ProjectID);
            paras[1] = new SqlParameter("@in_ProjectName", project.ProjectName);
            paras[2] = new SqlParameter("@in_ProcureStartTime", project.ProcureStartTime);
            paras[3] = new SqlParameter("@in_ProcureEndTime", project.ProcureEndTime);
            paras[4] = new SqlParameter("@out_success", SqlDbType.Int);
            paras[4].Direction = ParameterDirection.Output;
            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ProcurementProject_Add", paras);
            return int.Parse(paras[4].Value.ToString());
        }

        /// <summary>
        /// 更新一个采购项目
        /// </summary>
        public static int UpdateProcurementProject(ProcurementProject project)
        {
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@in_ProjectID", project.ProjectID);
            paras[1] = new SqlParameter("@in_ProjectName", project.ProjectName);
            paras[2] = new SqlParameter("@in_ProcureStartTime", project.ProcureStartTime);
            paras[3] = new SqlParameter("@in_ProcureEndTime", project.ProcureEndTime);
            paras[4] = new SqlParameter("@out_success", SqlDbType.Int);
            paras[4].Direction = ParameterDirection.Output;
            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ProcurementProject_Update", paras);

            return (int)paras[4].Value;
        }

        /// <summary>
        /// 获取一个采购项目
        /// </summary>
        public static ProcurementProject GetProcurementProjectByID(string projectID)
        {
            SqlParameter para = new SqlParameter("@ProjectID", projectID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, "SELECT TOP 1 * FROM TB_ProcurementProject WHERE ProjectID = @ProjectID", new SqlParameter[] { para });

            if (dt.Rows.Count == 0)
            {

                return null;
            }
            else
            {
                //return HZ.Data.DAL_Helper.GetProcurementProjectFromDataRow(dt.Rows[0]);
                return HZ.Data.DAL_Helper.CommonFill<ProcurementProject>(dt.Rows[0]);
            }


        }

        /// <summary>
        /// 获取所有采购项目
        /// </summary>
        public static IList<ProcurementProject> GetAllProcurementProject()
        {
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("SELECT * FROM TB_ProcurementProject");
            //return HZ.Data.DAL_Helper.GetProcurementProjectFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<ProcurementProject>(dt);
        }

        /// <summary>
        /// 采购项目条件查询（有效的【在采购项目有效期内】）
        /// </summary>
        /// <param name="project">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<ProcurementProject> GetProjectByWhere(ProcurementProject project, int currentPage, int pageSize, out int totalRecord)
        {
            StringBuilder where = new StringBuilder(512);
            where.Append("1=1");
            if (project != null)
            {
                if (!string.IsNullOrEmpty(project.ProjectID))
                {

                    where.AppendFormat(" AND ProjectID = to_date('{0}','yyyy-mm-dd hh24:Mi:ss')", project.ProjectID);
                }

                if (!string.IsNullOrEmpty(project.ProjectName))
                {

                    where.AppendFormat(" AND ProjectName LIKE '%{0}%' ", project.ProjectName);
                }

                if (project.ProcureStartTimeFrom != null)
                {

                    where.AppendFormat(" AND ProcureStartTime >= to_date('{0}','yyyy-mm-dd hh24:Mi:ss') ", project.ProcureStartTimeFrom.Value.ToShortDateString());
                }

                if (project.ProcureStartTimeTo != null)
                {

                    where.AppendFormat(" AND ProcureStartTime < to_date('{0}','yyyy-mm-dd hh24:Mi:ss') ", project.ProcureStartTimeTo.Value.AddDays(1).ToShortDateString());
                }

                if (project.ProcureEndTimeFrom != null)
                {
                    where.AppendFormat(" AND ProcureEndTime >= to_date('{0}','yyyy-mm-dd hh24:Mi:ss') ", project.ProcureEndTimeFrom.Value.ToShortDateString());
                }
                if (project.ProcureEndTimeTo != null)
                {

                    where.AppendFormat(" AND ProcureEndTime < to_date('{0}','yyyy-mm-dd hh24:Mi:ss') ", project.ProcureEndTimeTo.Value.AddDays(1).ToShortDateString());
                }
            }
            string sql = string.Format("SELECT * FROM TB_ProcurementProject WHERE {0} ORDER BY ProjectID", where.ToString());

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);

            totalRecord = pdes.TotalCount;
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            //return HZ.Data.DAL_Helper.GetProcurementProjectFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<ProcurementProject>(dt);

        }

        /// <summary>
        /// 采购项目条件查询（无效的【不在采购项目有效期内】）
        /// </summary>
        /// <param name="project">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<ProcurementProject> GetInvalidProjectByWhere(ProcurementProject project, int currentPage, int pageSize, out int totalRecord)
        {
            StringBuilder where = new StringBuilder(512);
            where.Append(" [ProcureStartTime]>sysdate OR [ProcureEndTime]<sysdate ");
            if (project != null)
            {
                if (!string.IsNullOrEmpty(project.ProjectID))
                {

                    where.AppendFormat(" AND ProjectID = '{0}'", project.ProjectID);
                }
                if (!string.IsNullOrEmpty(project.ProjectName))
                {

                    where.AppendFormat(" AND ProjectName LIKE '%{0}%' ", project.ProjectName);
                }
                if (project.ProcureStartTimeFrom != null)
                {

                    where.AppendFormat(" AND ProcureStartTime >= '{0}' ", project.ProcureStartTimeFrom.Value.ToShortDateString());
                }
                if (project.ProcureEndTimeTo != null)
                {

                    where.AppendFormat(" AND ProcureEndTime < '{0}' ", project.ProcureEndTimeTo.Value.AddDays(1).ToShortDateString());
                }
            }
            string sql = string.Format("SELECT * FROM TB_ProcurementProject WHERE {0} ORDER BY ProjectID", where.ToString());

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);

            totalRecord = pdes.TotalCount;
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            //return HZ.Data.DAL_Helper.GetProcurementProjectFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<ProcurementProject>(dt);
        }

        /// <summary>
        /// 删除一个采购项目
        /// </summary>
        /// <param name="projectID">项目ID</param>
        /// <returns>1成功 0 失败</returns>
        public static int DeleteProcurementProjectByID(string projectID)
        {
            SqlParameter[] paras = new SqlParameter[] 
            {
                new SqlParameter("@in_ProjectID", projectID),
                new SqlParameter("@out_success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;


            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ProcurementProject_Del", paras);

            return (int)paras[paras.Length - 1].Value;
        }

    }
}
